import pdfplumber
import pandas as pd
import os

if __name__ == "__main__":
    # 假设你的PDF文件都在这个文件夹下，你可以根据实际情况修改路径
    folder_path = "/Users/lhmily/Desktop/2024data/data/Food_Manual"
    pdf_files = [f for f in os.listdir(folder_path) if f.endswith(".pdf")]

    # 读取原始的data.xlsx文件（假设文件存在且格式正确）
  
    original_df = pd.read_excel('/Users/lhmily/Desktop/2024data/data/data.xlsx')

    # 用于存储提取的数据
    product_category_data = []
    tissue_status_data = []
    applicable_population_data = []

    for pdf_file in pdf_files:
        file_path = os.path.join(folder_path, pdf_file)
        try:
            with pdfplumber.open(file_path) as pdf:
                product_category = ""
                tissue_status = ""
                applicable_population = ""
                for page in pdf.pages:
                    text = page.extract_text()
                    if text:
                        if "产品类别" in text:
                            start_index = text.find("产品类别")
                            end_index = text.find("\n", start_index)
                            product_category = text[start_index + len("产品类别") + 1:end_index].strip()
                        if "组织状态" in text:
                            start_index = text.find("组织状态")
                            end_index = text.find("\n", start_index)
                            tissue_status = text[start_index + len("组织状态") + 1:end_index].strip()
                        if "适用人群" in text:
                            start_index = text.find("适用人群")
                            end_index = text.find("\n", start_index)
                            applicable_population = text[start_index + len("适用人群") + 1:end_index].strip()
                product_category_data.append(product_category)
                tissue_status_data.append(tissue_status)
                applicable_population_data.append(applicable_population)
        except FileNotFoundError:
            print(f"无法打开文件: {file_path}")

    # 处理数据列表，将空字符串替换为None（表示留空）
    product_category_data = [None if category == "" else category for category in product_category_data]
    tissue_status_data = [None if status == "" else status for status in tissue_status_data]
    applicable_population_data = [None if population == "" else population for population in applicable_population_data]

    # 在原始DataFrame中新增三列
    original_df["产品类别"] = product_category_data
    original_df["组织状态"] = tissue_status_data
    original_df["适用人群"] = applicable_population_data

    # 保存更新后的DataFrame到result2.xlsx
    result_file_path = os.path.join(folder_path, "result2.xlsx")
    original_df.to_excel(result_file_path, index=False)

    # 列出前5款特医食品的结果
    top_5 = original_df.head(5)
    print(top_5)